﻿SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[bht_Groups](
	[Id] [int] NOT NULL,
	[Name] [nvarchar](400) NOT NULL,
	[Identyficator] [nvarchar](400) NULL,
 CONSTRAINT [PK_bht_Groups] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE PROCEDURE [dbo].[bhp_NemoGroupGetAll]
AS
BEGIN

	SELECT 
		t.id as Id,
		g.name as Name,
		t.name as SystemName,
		g.Identyficator,
		t.name as SelectTable,
		CASE t.typ WHEN 'T' THEN 1 ELSE 0 END as AllowEdit
	FROM 
		n_Tree t
		LEFT JOIN bht_Groups g ON t.id = g.Id
	WHERE
		del = 0
		AND typ IN ('V', 'T')
		AND parent = 0

END



GO

CREATE PROCEDURE [dbo].[bhp_NemoGroupGet]
	@Id				INT
AS
BEGIN

	SELECT 
		t.id as Id,
		g.name as Name,
		t.name as SystemName,
		g.Identyficator,
		t.name as SelectTable,
		CASE t.typ WHEN 'T' THEN 1 ELSE 0 END as AllowEdit
	FROM 
		n_Tree t
		LEFT JOIN bht_Groups g ON t.id = g.Id
	WHERE
		(del IS NULL OR del = 0)
		AND typ IN ('V', 'T')
		AND parent = 0
		AND t.id = @Id

END


GO


CREATE PROCEDURE [dbo].[bhp_NemoGroupSchemeGet]
	@GroupId				INT
AS
BEGIN
	
	
	DECLARE
		@GroupName	NVARCHAR(100),
		@Sql		NVARCHAR(MAX)
		
	SELECT @GroupName = name FROM n_Tree WHERE id = @GroupId 
	
	SET @Sql = 'SELECT TOP 1 * FROM ' + @GroupName
	
	EXEC sp_executesql @Sql

END


GO


CREATE TABLE dbo.bht_ColumnTypes
	(
	ColumnType nvarchar(10) NOT NULL,
	EnumType nvarchar(50) NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.bht_ColumnTypes ADD CONSTRAINT
	PK_bht_ColumnTypes PRIMARY KEY CLUSTERED 
	(
	ColumnType
	) ON [PRIMARY]

GO
INSERT INTO bht_ColumnTypes VALUES ('F', 'ValueField')
GO
INSERT INTO bht_ColumnTypes VALUES ('F1', 'OneReference')
GO
INSERT INTO bht_ColumnTypes VALUES ('FN', 'ManyReference')
GO
INSERT INTO bht_ColumnTypes VALUES ('I', 'FileField')
GO
INSERT INTO bht_ColumnTypes VALUES ('D', 'DateField')
GO
INSERT INTO bht_ColumnTypes VALUES ('DT', 'DateTimeField')

GO

CREATE TABLE [dbo].[bht_Columns](
	[ColumnId] [int] NOT NULL,
	[DisplayName] [nvarchar](400) NOT NULL,
	[FormattingValue] [nvarchar](400) NULL,
	[RegexValidation] [nvarchar](100) NULL,
	[Description] [nvarchar](400) NULL,
	[HideForEdition] [bit] NOT NULL,
	[CustomEditControl] [nvarchar](100) NULL,
 CONSTRAINT [PK_bht_NemoColumns] PRIMARY KEY CLUSTERED 
(
	[ColumnId] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[bht_Columns]  WITH CHECK ADD  CONSTRAINT [FK_bht_NemoColumns_bht_NemoColumns] FOREIGN KEY([ColumnId])
REFERENCES [dbo].[bht_Columns] ([ColumnId])
GO

ALTER TABLE [dbo].[bht_Columns] CHECK CONSTRAINT [FK_bht_NemoColumns_bht_NemoColumns]
GO

ALTER TABLE [dbo].[bht_Columns] ADD  CONSTRAINT [DF_bht_Columns_HideForEdition]  DEFAULT ((0)) FOR [HideForEdition]
GO

CREATE PROCEDURE [dbo].[bhp_NemoUsers]
AS
BEGIN

	SELECT 
		idx as UserId,
		identyfikator as Name
	FROM 
		SYS_UZYTKOWNIK
	WHERE
		identyfikator IS NOT NULL
	UNION 
	SELECT 
		3000000,
		'Admin'

END
GO




CREATE PROCEDURE [dbo].[bhp_NemoGroupColumnsGet]
	@GroupId				INT
AS
BEGIN
	
	
	SELECT 
		id as ColumnId,
		name as ColumnName,
		ct.EnumType as ColumnType,
		ISNULL(t.rel, 0) as RelatedGroupId,
		ISNULL(c.DisplayName, name) as DisplayName,
		ISNULL(c.FormattingValue, '{0}') as FormattingValue,
		0 as ColumnSort,
		c.RegexValidation,
		c.[Description],
		ISNULL(c.HideForEdition, 0) as HideForEdition,
		c.CustomEditControl
	FROM
		n_Tree t
		INNER JOIN bht_ColumnTypes ct ON t.typ = ct.ColumnType
		LEFT JOIN bht_Columns c ON t.id = c.ColumnId
	WHERE
		parent = @GroupId
		AND t.del = 0

END

GO

CREATE PROCEDURE [dbo].[bhp_NemoGroupSystemNamesGet]
AS
BEGIN
	
	
	SELECT
		id as GroupId,
		name as SystemName
	FROM
		n_Tree
	WHERE
		(del IS NULL OR del = 0)
		AND typ IN ('T', 'V')

END

GO
